package com.borball.test.functional;

import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import javax.sql.DataSource;

import org.dbunit.DatabaseUnitException;
import org.dbunit.database.IDatabaseConnection;
import org.dbunit.dataset.IDataSet;
import org.dbunit.dataset.xml.FlatXmlDataSetBuilder;
import org.dbunit.ext.h2.H2Connection;
import org.dbunit.operation.DatabaseOperation;
import org.springframework.core.io.DefaultResourceLoader;
import org.springframework.core.io.ResourceLoader;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

public abstract class Fixtures {

	private static ResourceLoader resourceLoader = new DefaultResourceLoader();

	public static void loadData(DataSource h2DataSource, String... xmlFilePaths) throws Exception {
		execute(DatabaseOperation.INSERT, h2DataSource, xmlFilePaths);
	}

	public static void reloadData(DataSource h2DataSource, String... xmlFilePaths) throws Exception {
		execute(DatabaseOperation.CLEAN_INSERT, h2DataSource, xmlFilePaths);
	}

	public static void reloadAllTable(DataSource h2DataSource, String... xmlFilePaths) throws Exception {
		deleteAllTable(h2DataSource);
		loadData(h2DataSource, xmlFilePaths);
	}

	public static void deleteData(DataSource h2DataSource, String... xmlFilePaths) throws Exception {
		execute(DatabaseOperation.DELETE_ALL, h2DataSource, xmlFilePaths);
	}

	private static void execute(DatabaseOperation operation, DataSource h2DataSource, String... xmlFilePaths)
			throws DatabaseUnitException, SQLException {
		IDatabaseConnection connection = new H2Connection(h2DataSource.getConnection(), null);

		for (String xmlPath : xmlFilePaths) {
			try {
				InputStream input = resourceLoader.getResource(xmlPath).getInputStream();
				IDataSet dataSet = new FlatXmlDataSetBuilder().setColumnSensing(true).build(input);
				operation.execute(connection, dataSet);
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}

	public static void deleteAllTable(DataSource h2DataSource, String... excludeTables) {

		List<String> tableNames = new ArrayList<String>();
		try {
			ResultSet rs = h2DataSource.getConnection().getMetaData()
					.getTables(null, null, null, new String[] { "TABLE" });
			while (rs.next()) {
				String tableName = rs.getString("TABLE_NAME");
				if (Arrays.binarySearch(excludeTables, tableName) < 0) {
					tableNames.add(tableName);
				}
			}

			deleteTable(h2DataSource, tableNames.toArray(new String[tableNames.size()]));
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static void deleteTable(DataSource h2DataSource, String... tableNames) {
		SimpleJdbcTemplate template = new SimpleJdbcTemplate(h2DataSource);

		//template.update("SET REFERENTIAL_INTEGRITY FALSE");

		for (String tableName : tableNames) {
			template.update("DELETE FROM " + tableName);
		}

		//template.update("SET REFERENTIAL_INTEGRITY TRUE");
	}
}
